package com.LGF.Book.dao;

import com.LGF.Book.bean.Book;
import com.LGF.Book.bean.BookType;
import com.LGF.Book.util.DBHelper;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class BookDao {
    //创建QueryRunner对象
    QueryRunner runner = new QueryRunner();

    /**
     * 根据图书类型模糊查询
     * @param tname
     * @return
     * @throws SQLException
     */
    public List<Map<String,Object>> selectTname( String tname) throws SQLException {
        Connection conn = DBHelper.getConnection();
        String sql="select * from selectTypeView where tname like '%' ? '%'";
        List<Map<String, Object>> data = runner.query(conn, sql, new MapListHandler(),tname);
        DBHelper.close(conn);
        return data;
    }

    /**
     * 根据类型id查询图书信息
     * @param typeId
     * @return
     * @throws SQLException
     */
    public Book getBytypeId(long typeId) throws SQLException {
        Connection conn = DBHelper.getConnection();
        String sql ="select * from book where typeId=?";
        Book book = runner.query(conn, sql, new BeanHandler<Book>(Book.class), typeId);
        DBHelper.close(conn);
        return book;
    }

    /**
     * 根据书名模糊查询图书信息
     * @param bname
     * @return
     * @throws SQLException
     */
    public List<Book> selectByBname(String bname) throws SQLException {
        Connection conn = DBHelper.getConnection();
        String sql ="select * from book where bname  like '%' ? '%'";
        List<Book> books = runner.query(conn, sql, new BeanListHandler<Book>(Book.class), bname);
        return books;
    }

    /**
     * 根据Id模糊查询图书信息
     * @param id
     * @return
     * @throws SQLException
     */
    public List<Book> selectById(String id) throws SQLException {
        Connection conn = DBHelper.getConnection();
        String sql ="select * from book where id  like '%' ? '%'";
        List<Book> books = runner.query(conn, sql, new BeanListHandler<Book>(Book.class), id);
        return books;
    }

    /**
     * 根据Id获取图书信息
     * @param id
     * @return
     * @throws SQLException
     */
    public Book getById(long id) throws SQLException {
        Connection conn = DBHelper.getConnection();
        String sql = "select * from book where id=?";
        Book book = runner.query(conn, sql, new BeanHandler<Book>(Book.class), id);
        DBHelper.close(conn);
        return book;
    }

    /**
     * 查询所有图书信息
     * @return
     * @throws SQLException
     */
    public List<Book> getAll() throws SQLException {
        Connection conn = DBHelper.getConnection();
        String sql = "select * from book";
        List<Book> bookList = runner.query(conn, sql, new BeanListHandler<Book>(Book.class));
        DBHelper.close(conn);
        return bookList;
    }

    /**
     * 分页查询图书信息
     * @param page
     * @param limit
     * @return
     * @throws SQLException
     */
    public List<Book> getPage(int page,int limit) throws SQLException {
        Connection conn = DBHelper.getConnection();
        String sql = "select * from book  limit ?,?";
        List<Book> bookList = runner.query(conn, sql, new BeanListHandler<Book>(Book.class),page,limit);
        DBHelper.close(conn);
        return bookList;
    }

    /**
     * 添加图书信息
     * @param typeId
     * @param bname
     * @param price
     * @param desc
     * @param pic
     * @param publish
     * @param author
     * @param stock
     * @param address
     * @return
     * @throws SQLException
     */
    public int add(long typeId,String bname,float price, String desc,String pic,String publish,String author,long stock,String address) throws SQLException {
        Connection conn = DBHelper.getConnection();
        String sql = "insert into book(typeid,bname,price,`desc`,pic,publish,author,stock,address) values(?,?,?,?,?,?,?,?,?)";
        int count = runner.update(conn, sql, typeId, bname, price, desc, pic, publish, author, stock, address);
        DBHelper.close(conn);
        return count;

    }

    /**
     * 修改图书信息
     * @param id
     * @param typeId
     * @param bname
     * @param price
     * @param desc
     * @param pic
     * @param publish
     * @param author
     * @param stock
     * @param address
     * @return
     * @throws SQLException
     */
    public int modify(long id,long typeId,String bname,float price, String desc,String pic,String publish,String author,long stock,String address) throws SQLException {
        Connection conn = DBHelper.getConnection();
        String sql = "update book set bname=?, typeId=?,author=?,price=?,pic=?,publish=?,stock=?,address=?,`desc`=? where id=?";
        int count = runner.update(conn, sql, bname, typeId,author, price,pic, publish,stock,address,desc,id);
        DBHelper.close(conn);
        return count;


    }

    /**
     * 根据图书id修改图书库存量
     * @param bookId
     * @param stock
     * @return
     * @throws SQLException
     */
    public int modify(long bookId,long stock) throws SQLException {
        Connection conn = DBHelper.getConnection();
        String sql ="update book set stock = stock+? where id=?";
        int count = runner.update(conn, sql, stock, bookId);
        return count;
    }

    /**
     * 删除图书信息
     * @param id
     * @return
     * @throws SQLException
     */
    public int remove(long id) throws SQLException {
        Connection conn = DBHelper.getConnection();
        String sql ="delete from book where id=?";
        int count = runner.update(conn, sql, id);
        DBHelper.close(conn);
        return count;
    }


}
